{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "99512c9b-e4c1-4249-9598-9b2eb77f2b05",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-07-01T11:18:57.381810Z",
     "start_time": "2025-07-01T11:18:55.587648Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "【开始】加载原始数据...\n",
      " 开始时间：2025-07-01 19:33:36.932835\n",
      " 原始数据加载完成：共 1581278 行，26 列\n",
      " 已采样前 100000 条数据\n",
      "\n",
      " 数据质量报告：\n",
      "                         Missing Count  Missing Rate (%)  Unique Count  \\\n",
      "MktCoupons                           0               0.0             3   \n",
      "OriginCityMarketID                   0               0.0            38   \n",
      "DestCityMarketID                     0               0.0            43   \n",
      "OriginAirportID                      0               0.0            56   \n",
      "DestAirportID                        0               0.0            61   \n",
      "Carrier                              0               0.0            23   \n",
      "NonStopMiles                         0               0.0           244   \n",
      "RoundTrip                            0               0.0             2   \n",
      "ODPairID                             0               0.0            87   \n",
      "Pax                                  0               0.0            87   \n",
      "CarrierPax                           0               0.0           264   \n",
      "Average_Fare                         0               0.0           413   \n",
      "Market_share                         0               0.0           261   \n",
      "Market_HHI                           0               0.0            84   \n",
      "LCC_Comp                             0               0.0             2   \n",
      "Multi_Airport                        0               0.0             2   \n",
      "Circuity                             0               0.0          1638   \n",
      "Slot                                 0               0.0             2   \n",
      "Non_Stop                             0               0.0             2   \n",
      "MktMilesFlown                        0               0.0            87   \n",
      "OriginCityMarketID_freq              0               0.0            38   \n",
      "DestCityMarketID_freq                0               0.0            43   \n",
      "OriginAirportID_freq                 0               0.0            56   \n",
      "DestAirportID_freq                   0               0.0            61   \n",
      "Carrier_freq                         0               0.0            23   \n",
      "ODPairID_freq                        0               0.0            87   \n",
      "\n",
      "                        Data Type  \n",
      "MktCoupons                  int64  \n",
      "OriginCityMarketID          int64  \n",
      "DestCityMarketID            int64  \n",
      "OriginAirportID             int64  \n",
      "DestAirportID               int64  \n",
      "Carrier                     int64  \n",
      "NonStopMiles              float64  \n",
      "RoundTrip                 float64  \n",
      "ODPairID                    int64  \n",
      "Pax                       float64  \n",
      "CarrierPax                float64  \n",
      "Average_Fare              float64  \n",
      "Market_share              float64  \n",
      "Market_HHI                float64  \n",
      "LCC_Comp                    int64  \n",
      "Multi_Airport               int64  \n",
      "Circuity                  float64  \n",
      "Slot                        int64  \n",
      "Non_Stop                  float64  \n",
      "MktMilesFlown             float64  \n",
      "OriginCityMarketID_freq   float64  \n",
      "DestCityMarketID_freq     float64  \n",
      "OriginAirportID_freq      float64  \n",
      "DestAirportID_freq        float64  \n",
      "Carrier_freq              float64  \n",
      "ODPairID_freq             float64  \n",
      "\n",
      " 元信息记录：\n",
      " - source_file: data/processed/MarketFarePredictionData.csv\n",
      " - sample_size: 100000\n",
      " - columns_count: 26\n",
      " - loaded_time: 2025-07-01 19:33:36\n",
      " - output_file: data/raw/raw_data.parquet\n",
      " 数据已保存至：data/raw/raw_data.parquet\n",
      "【完成】数据加载流程结束\n",
      "\n",
      " 数据预览：\n",
      "   MktCoupons  OriginCityMarketID  DestCityMarketID  OriginAirportID  \\\n",
      "0           2                 178               152              170   \n",
      "1           2                 178               152              170   \n",
      "2           2                 178               152              170   \n",
      "3           2                 178               152              170   \n",
      "4           2                 178               152              170   \n",
      "\n",
      "   DestAirportID  Carrier  NonStopMiles  RoundTrip  ODPairID    Pax  ...  \\\n",
      "0            255        6        1807.0        1.0      4035  136.0  ...   \n",
      "1            194       20        1798.0        1.0      4035  136.0  ...   \n",
      "2            260        6        1784.0        0.0      4035  136.0  ...   \n",
      "3            255        6        1807.0        1.0      4035  136.0  ...   \n",
      "4            194       20        1798.0        1.0      4035  136.0  ...   \n",
      "\n",
      "   Circuity  Slot  Non_Stop  MktMilesFlown  OriginCityMarketID_freq  \\\n",
      "0  1.367460     0       0.0    1992.449761                 0.004138   \n",
      "1  1.051724     0       0.0    1992.449761                 0.004138   \n",
      "2  1.034753     0       0.0    1992.449761                 0.004138   \n",
      "3  1.029884     0       0.0    1992.449761                 0.004138   \n",
      "4  1.062291     0       0.0    1992.449761                 0.004138   \n",
      "\n",
      "   DestCityMarketID_freq  OriginAirportID_freq  DestAirportID_freq  \\\n",
      "0               0.039783              0.004138            0.022049   \n",
      "1               0.039783              0.004138            0.008368   \n",
      "2               0.039783              0.004138            0.009366   \n",
      "3               0.039783              0.004138            0.022049   \n",
      "4               0.039783              0.004138            0.008368   \n",
      "\n",
      "   Carrier_freq  ODPairID_freq  \n",
      "0      0.116826       0.000132  \n",
      "1      0.307651       0.000132  \n",
      "2      0.116826       0.000132  \n",
      "3      0.116826       0.000132  \n",
      "4      0.307651       0.000132  \n",
      "\n",
      "[5 rows x 26 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "from datetime import datetime\n",
    "\n",
    "def load_data(file_path, sample_size=None, output_path=None):\n",
    "    print(\"【开始】加载原始数据...\")\n",
    "\n",
    "    start_time = datetime.now()\n",
    "    print(f\" 开始时间：{start_time}\")\n",
    "\n",
    "    df = pd.read_csv(file_path)\n",
    "    original_shape = df.shape\n",
    "    print(f\" 原始数据加载完成：共 {original_shape[0]} 行，{original_shape[1]} 列\")\n",
    "\n",
    "    if sample_size:\n",
    "        df = df.head(sample_size).copy()\n",
    "        print(f\" 已采样前 {sample_size} 条数据\")\n",
    "\n",
    "    missing_summary = df.isnull().sum()\n",
    "    missing_rate = (missing_summary / df.shape[0]) * 100\n",
    "    quality_report = pd.DataFrame({\n",
    "        'Missing Count': missing_summary,\n",
    "        'Missing Rate (%)': missing_rate,\n",
    "        'Unique Count': df.nunique(),\n",
    "        'Data Type': df.dtypes\n",
    "    })\n",
    "    print(\"\\n 数据质量报告：\")\n",
    "    print(quality_report)\n",
    "\n",
    "    metadata = {\n",
    "        \"source_file\": file_path,\n",
    "        \"sample_size\": sample_size if sample_size else original_shape[0],\n",
    "        \"columns_count\": original_shape[1],\n",
    "        \"loaded_time\": start_time.strftime(\"%Y-%m-%d %H:%M:%S\"),\n",
    "        \"output_file\": output_path if output_path else \"Not saved\"\n",
    "    }\n",
    "    print(\"\\n 元信息记录：\")\n",
    "    for key, value in metadata.items():\n",
    "        print(f\" - {key}: {value}\")\n",
    "\n",
    "    if output_path:\n",
    "        os.makedirs(os.path.dirname(output_path), exist_ok=True)\n",
    "        df.to_parquet(output_path)\n",
    "        print(f\" 数据已保存至：{output_path}\")\n",
    "\n",
    "    print(\"【完成】数据加载流程结束\")\n",
    "    return df\n",
    "\n",
    "# 示例调用\n",
    "input_path = \"data/processed/MarketFarePredictionData.csv\"\n",
    "output_path = \"data/raw/raw_data.parquet\"\n",
    "raw_data = load_data(input_path, sample_size=100_000, output_path=output_path)\n",
    "print(\"\\n 数据预览：\")\n",
    "print(raw_data.head())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "7c96a51a-9864-4051-9820-b39bf460941d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sklearn.impute import SimpleImputer\n",
    "from sklearn.preprocessing import StandardScaler, OneHotEncoder\n",
    "from sklearn.compose import ColumnTransformer\n",
    "from sklearn.pipeline import Pipeline\n",
    "import os\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "\n",
    "def detect_outliers(df, numeric_features, method='iqr'):\n",
    "    \"\"\"\n",
    "    使用箱线图方法识别数值型字段的异常值\n",
    "    :param df: 输入DataFrame\n",
    "    :param numeric_features: 数值列列表\n",
    "    :param method: 检测方法，默认 'iqr'\n",
    "    :return: 包含异常统计的字典\n",
    "    \"\"\"\n",
    "    outlier_report = {}\n",
    "\n",
    "    # 创建输出目录\n",
    "    os.makedirs(\"reports\", exist_ok=True)\n",
    "\n",
    "    for col in numeric_features:\n",
    "        q1 = df[col].quantile(0.25)\n",
    "        q3 = df[col].quantile(0.75)\n",
    "        iqr = q3 - q1\n",
    "        lower_bound = q1 - 1.5 * iqr\n",
    "        upper_bound = q3 + 1.5 * iqr\n",
    "\n",
    "        outliers = df[(df[col] < lower_bound) | (df[col] > upper_bound)]\n",
    "        outlier_count = len(outliers)\n",
    "        outlier_report[col] = {\n",
    "            \"outlier_count\": outlier_count,\n",
    "            \"lower_bound\": lower_bound,\n",
    "            \"upper_bound\": upper_bound\n",
    "        }\n",
    "\n",
    "        print(f\"🔍 {col} 发现 {outlier_count} 个异常值（阈值：{lower_bound:.2f} ~ {upper_bound:.2f}）\")\n",
    "\n",
    "        # 绘制箱线图并保存\n",
    "        plt.figure(figsize=(6, 2))\n",
    "        sns.boxplot(x=df[col])\n",
    "        plt.title(f\"{col} Boxplot\")\n",
    "        plt.tight_layout()\n",
    "        plt.savefig(f\"reports/outliers_{col}.png\")\n",
    "        plt.close()\n",
    "\n",
    "    return outlier_report\n",
    "\n",
    "\n",
    "def preprocess_data(df):\n",
    "    \"\"\"\n",
    "    数据预处理流程：\n",
    "        - 缺失值填充\n",
    "        - 标准化数值型字段\n",
    "        - One-Hot编码分类变量\n",
    "        - 去重处理\n",
    "        - 异常值检测\n",
    "    :param df: 原始DataFrame\n",
    "    :return: 预处理后的DataFrame\n",
    "    \"\"\"\n",
    "    print(\"【开始】数据预处理...\")\n",
    "\n",
    "    # 显式复制一份，避免后续警告\n",
    "    df = df.copy()\n",
    "\n",
    "    # 去重处理\n",
    "    if df.duplicated().sum() > 0:\n",
    "        print(f\"✂️ 检测到 {df.duplicated().sum()} 条重复记录，正在删除...\")\n",
    "        df = df.drop_duplicates()\n",
    "    else:\n",
    "        print(\"✅ 未发现重复记录\")\n",
    "\n",
    "    # 手动定义分类列\n",
    "    categorical_features = [\n",
    "        'Carrier', 'RoundTrip', 'ODPairID', 'LCC_Comp', 'Multi_Airport',\n",
    "        'Slot', 'Non_Stop', 'OriginCityMarketID', 'DestCityMarketID',\n",
    "        'OriginAirportID', 'DestAirportID'\n",
    "    ]\n",
    "\n",
    "    # 显式转换为字符串类型（避免 FutureWarning）\n",
    "    df[categorical_features] = df[categorical_features].apply(\n",
    "        lambda col: col.astype(str, errors='ignore')\n",
    "    )\n",
    "\n",
    "    # 自动识别数值列\n",
    "    numeric_features = df.drop(columns=categorical_features).select_dtypes(include=[np.number]).columns.tolist()\n",
    "\n",
    "    print(\"✅ 分类变量：\", categorical_features)\n",
    "    print(\"✅ 数值变量：\", numeric_features)\n",
    "\n",
    "    # 缺失值填充 + 标准化\n",
    "    numeric_transformer = Pipeline(steps=[\n",
    "        ('imputer', SimpleImputer(strategy='median')),\n",
    "        ('scaler', StandardScaler())])\n",
    "\n",
    "    # 缺失值填充 + One-Hot编码\n",
    "    categorical_transformer = Pipeline(steps=[\n",
    "        ('imputer', SimpleImputer(strategy='constant', fill_value='Unknown')),\n",
    "        ('onehot', OneHotEncoder(handle_unknown='ignore'))])\n",
    "\n",
    "    preprocessor = ColumnTransformer(\n",
    "        transformers=[\n",
    "            ('num', numeric_transformer, numeric_features),\n",
    "            ('cat', categorical_transformer, categorical_features)])\n",
    "\n",
    "    processed_array = preprocessor.fit_transform(df)\n",
    "\n",
    "    # 如果是稀疏矩阵，转换为 numpy array\n",
    "    try:\n",
    "        processed_array = processed_array.toarray()\n",
    "    except AttributeError:\n",
    "        pass\n",
    "\n",
    "    # 获取 OneHotEncoder 的特征名\n",
    "    cat_pipeline = preprocessor.named_transformers_['cat']\n",
    "    onehot_step = cat_pipeline.named_steps['onehot']\n",
    "    feature_names_cat = onehot_step.get_feature_names_out().tolist() if hasattr(onehot_step, 'get_feature_names_out') else []\n",
    "\n",
    "    # 拼接特征名\n",
    "    feature_names = numeric_features + feature_names_cat\n",
    "\n",
    "    # 转换为 DataFrame\n",
    "    df_processed = pd.DataFrame(processed_array, columns=feature_names)\n",
    "    print(\"📊 processed_array shape:\", df_processed.shape)\n",
    "\n",
    "    # 异常值检测\n",
    "    print(\"\\n🔍 开始异常值检测...\")\n",
    "    detect_outliers(df[numeric_features], numeric_features)\n",
    "\n",
    "    # 数据质量报告（预处理后）\n",
    "    print(\"\\n📊 预处理后数据质量报告：\")\n",
    "    missing_summary = df_processed.isnull().sum()\n",
    "    missing_rate = (missing_summary / df_processed.shape[0]) * 100\n",
    "    quality_report = pd.DataFrame({\n",
    "        'Missing Count': missing_summary,\n",
    "        'Missing Rate (%)': missing_rate,\n",
    "        'Unique Count': df_processed.nunique(),\n",
    "        'Data Type': df_processed.dtypes\n",
    "    })\n",
    "    print(quality_report)\n",
    "\n",
    "    print(\"【完成】数据预处理完成\")\n",
    "    return df_processed, preprocessor\n",
    "    raw_df = pd.read_csv(\"data/processed/MarketFarePredictionData.csv\")\n",
    "    sample_df = raw_df.head(100_000).copy()\n",
    "\n",
    "    print(\"🔄 正在进行数据预处理...\")\n",
    "    cleaned_df, _ = preprocess_data(sample_df)\n",
    "\n",
    "    output_path = \"data/preprocessed/cleaned_data.parquet\"\n",
    "    os.makedirs(os.path.dirname(output_path), exist_ok=True)\n",
    "    cleaned_df.to_parquet(output_path)\n",
    "    print(f\"✅ 已保存预处理后的数据到 {output_path}\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d190183-1b8d-4f9b-b29f-0c8861986656",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c2e49bce-cafc-4f4d-8814-9e2ef1eee935",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "148a9bba-e64c-49f2-985d-af3fc88c416f",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
